#!/usr/bin/env node

const Koa = require('koa');
const Router = require('koa-router');
const BodyParser = require('koa-bodyparser');
const mysql = require('mysql2/promise');
const loadRouters = require('require-directory');
const koaBody = require('koa-body')
const fs = require('fs')
const path = require('path')
const mime = require('mime-types');

const app = new Koa();
const router = new Router();
const bodyParser = new BodyParser();

const connection= mysql.createPool({
  host:'101.34.58.111',
  user:'root',
  password:'shangan',
  database:'上岸'
})

//设置跨域资源请求的问题
app.use(async (ctx,next) => {
  ctx.set('Access-Control-Allow-Origin', '*');
  ctx.set('Access-Control-Allow-Headers','Content-Type, Content-Length, Authorization, Accept, X-Requested-With,yourHeaderFeild'
    );
  ctx.set('Access-Control-Allow-Methods', 'PUT, POST, GET, DELETE');
  if(ctx.method == 'OPTIONS'){
    ctx.body = 200;
  }else{
    await next();  }
})
//设置上传文件的问题
app.use(koaBody({
  multipart:true
}))

/******************获得数据*************************/
//用户
router.get('/',async (ctx,next) => {
  let sql = 'select * from user';
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

//每日打卡
router.get('/checkmes',async(ctx,next) => {
  let sql = 'select * from checkmes'
  let res = await connection.execute(sql);
  let arr = res[0];
  ctx.body = JSON.stringify(arr);
})

//招生简章
router.get('/schoolrecruit',async(ctx,next) => {
  let sql = 'select * from schoolrecruit'
  let res = await connection.execute(sql);
  let arr = res[0];
  ctx.body = JSON.stringify(arr);
})

//学校分数线
router.get('/schoolscore',async(ctx,next) => {
  let sql = 'select * from schoolscore'
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})
//学校报录比
router.get('/schoolnumber',async(ctx,next) => {
  let sql = 'select * from schoolnumber'
  let res = await connection.execute(sql);
  console.log(res[0])
  ctx.body = JSON.stringify(res[0]);
})

//考研常识
router.get('/examknowledge',async(ctx,next) => {
  let sql = 'select * from examknowledge'
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

//考研政策
router.get('/exampolicy',async(ctx,next) => {
  let sql = 'select * from exampolicy'
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

//选择院校
router.get('/examchoose',async(ctx,next) => {
  let sql = 'select * from examchoose'
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

//备考指南
router.get('/examguide',async(ctx,next) => {
  let sql = 'select * from examguide'
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

//参考书
router.get('/book',async(ctx,next) => {
  let sql = 'select * from book'
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

//学习
router.get('/examexperience',async(ctx,next) => {
  let sql = 'select * from examexperience'
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

//圈子
router.get('/examcircle',async(ctx,next) => {
  let sql = 'select * from examcircle'
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

//系统管理
router.get('/admin',async(ctx,next) => {
  let sql = 'select adminImg,adminName,adminAddr,adminTno from admin';
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

//登陆用户信息
router.get('/usermes',async(ctx,next) => {
  let sql = 'select * from usermes';
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

//我的消息
router.get('/usermessage',async(ctx,next) => {
  let sql = 'select * from usermessage';
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

//我的创作
router.get('/usercompose',async(ctx,next) => {
  let sql = 'select * from usercompose';
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

//我的收藏
router.get('/usercollect',async(ctx,next) => {
  let sql = 'select * from usercollect';
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

//浏览记录
router.get('/userhistory',async(ctx,next) => {
  let sql = 'select * from userhistory';
  let res = await connection.execute(sql);
  ctx.body = JSON.stringify(res[0]);
})

/**************************新建表单中的insert*******************************/
//用户管理
// router.post('/',async (ctx,next) => {
//   let obj = ctx.request.body;
//   let str = '';
//   let arr = []
//   for(let i in obj){
//     if(i == 'upload'){
//       arr.push('UserImg')
//       str = str + `'${obj[i][0].response.addr}',`
//     }else{
//       arr.push(i)
//       str = str + `'${obj[i]}',`
//     }
//   }
//   let x = str.slice(0,str.length-1);
//   let sql = `INSERT INTO user(${arr}) VALUES(${x}) `
//   await connection.execute(sql)
//   ctx.body = JSON.stringify("success");
// })
// //城市文化列表管理
// router.post('/city/citycul',async (ctx,next) => {
//   let obj = ctx.request.body;
//   let str = '';
//   let arr = []
//   for(let i in obj){
//     if(i == 'upload'){
//       arr.push('cultureImg')
//       if(obj[i].length == 1){
//         str = str + `'${obj[i][0].response.addr}',`
//       }else{
//         let str0 = '';
//         for(let j = 0 ; j< obj[i].length;j++){
//           str0 = str0 + `${obj[i][j].response.addr};`
//         }
//         console.log(str0)
//         str = str + `'${str0}',`
//       }
//     }else{
//       arr.push(i)
//       str = str + `'${obj[i]}',`
//     }
//   }
//   let x = str.slice(0,str.length-1);
//   let sql = `INSERT INTO culture(${arr}) VALUES(${x}) `
//   console.log(sql)
//   await connection.execute(sql); 
//   ctx.body = JSON.stringify("success");
// })
// //景点推荐
// router.post('/city/interest',async(ctx,next) => {
//   let obj = ctx.request.body;
//   let str = '';
//   let arr = []
//   for(let i in obj){
//     if(i == 'upload'){
//       arr.push('placeImg')
//       if(obj[i].length == 1){
//         str = str + `'${obj[i][0].response.addr}',`
//       }else{
//         let str0 = '';
//         for(let j = 0 ; j< obj[i].length;j++){
//           str0 = str0 + `${obj[i][j].response.addr};`
//         }
//         console.log(str0)
//         str = str + `'${str0}'`
//       }
//     }else{
//       arr.push(i)
//       str = str + `'${obj[i]}',`
//     }
    
    
//   }
//   let x = str.slice(0,str.length-1);
//   let sql = `INSERT INTO place(${arr}) VALUES (${x})`
//   console.log(sql)
//   await connection.execute(sql); 
//   ctx.body = JSON.stringify("success");
// })
// //周边推荐
// router.post('/city/enviro',async(ctx,next) => {
//   let obj = ctx.request.body;
//   let str = '';
//   let arr = []
//   for(let i in obj){
//     if(i == 'upload'){
//       arr.push('recomImg')
//       if(obj[i].length == 1){
//         str = str + `'${obj[i][0].response.addr}',`
//       }else{
//         let str0 = '';
//         for(let j = 0 ; j< obj[i].length;j++){
//           str0 = str0 + `${obj[i][j].response.addr};`
//         }
//         console.log(str0)
//         str = str + `'${str0}'`
//       }
//     }else{
//       arr.push(i)
//       str = str + `'${obj[i]}',`
//     }
//   }
//   let x = str.slice(0,str.length-1);
//   let sql = `INSERT INTO recommend(${arr}) VALUES(${x})`
//   await connection.execute(sql); 
//   ctx.body = JSON.stringify("success");
// })
// //美食推荐管理
// router.post('/city/food',async(ctx,next) => {
//   let obj = ctx.request.body;
//   let str = '';
//   let arr = []
//   for(let i in obj){
//     if(i == 'upload'){
//       arr.push('foodImg')
//       if(obj[i].length == 1){
//         str = str + `'${obj[i][0].response.addr}',`
//       }else{
//         let str0 = '';
//         for(let j = 0 ; j< obj[i].length;j++){
//           str0 = str0 + `${obj[i][j].response.addr};`
//         }
//         console.log(str0)
//         str = str + `'${str0}'`
//       }
//     }else{
//       arr.push(i)
//       str = str + `'${obj[i]}',`
//     }
//   }
  
//   let x = str.slice(0,str.length-1);
//   let sql = `INSERT INTO food(${arr}) VALUES (${x})`
//   await connection.execute(sql); 
//   ctx.body = JSON.stringify("success");
// })
// //酒店推荐管理
// router.post('/city/hotel',async(ctx,next) => {
//   let obj = ctx.request.body;
//   console.log(obj)
//   let str = '';
//   let arr = []
//   for(let i in obj){
//     if(i == 'upload'){
//       arr.push('pubImg')
//       if(obj[i].length == 1){
//         str = str + `'${obj[i][0].response.addr}',`
//       }else{
//         let str0 = '';
//         for(let j = 0 ; j< obj[i].length;j++){
//           str0 = str0 + `${obj[i][j].response.addr};`
//         }
//         console.log(str0)
//         str = str + `'${str0}'`
//       }
      
//     }else{
//       arr.push(i)
//       str = str + `'${obj[i]}',`
//     }
//   }

//   console.log(arr);
//   let x = str.slice(0,str.length-1);
//   let sql = `INSERT INTO pub(${arr}) VALUES (${x})`
//   // await connection.execute(sql); 
//   console.log(sql)
//   ctx.body = JSON.stringify("success");
// })


// /***********************删除的操作*****************************/
// router.delete('/:Userwxno',async (ctx,next) => {
//   let obj = ctx.params;
//   let Userwxno = obj.Userwxno;
//   console.log(Userwxno)
//   let sql = `DELETE FROM user WHERE Userwxno = '${Userwxno}'`
//     console.log(sql)
//   await connection.execute(sql)
//   ctx.body = JSON.stringify("success");
// })

// //城市文化列表管理
// router.delete('/city/citycul/:cultureID/:cityID',async (ctx,next) => {
//   let obj = ctx.params;
//   let cultureID = obj.cultureID;
//   let cityID = obj.cityID;
//   let sql = `DELETE FROM culture WHERE cultureID = '${cultureID}' and cityID = '${cityID}'`
//   await connection.execute(sql); 
//   ctx.body = JSON.stringify("success");
// })
// //景点推荐
// router.delete('/city/interest/:placeID/:cityID',async(ctx,next) => {
//   let obj = ctx.params;
//   let placeID = obj.placeID;
//   let cityID = obj.cityID;
//   let sql = `DELETE FROM place WHERE placeID = '${placeID}' and cityID = '${cityID}'`
//    await connection.execute(sql); 
//   ctx.body = JSON.stringify("success");
// })

// //周边推荐
// router.delete('/city/enviro/:recomID/:cityID',async(ctx,next) => {
//   let obj = ctx.params;
//   let recomID = obj.recomID;
//   let cityID = obj.cityID;
//   let sql = `DELETE FROM recommend WHERE recomID = '${recomID}' and cityID = '${cityID}'`
//   await connection.execute(sql); 
//   ctx.body = JSON.stringify("success");
// })
// //美食推荐管理
// router.delete('/city/food/:foodID/:cityID',async(ctx,next) => {
//   let obj = ctx.params;
//   let foodID = obj.foodID;
//   let cityID = obj.cityID;
//   console.log(obj)
//   let sql = `DELETE FROM food WHERE foodID = '${foodID}' and cityID = '${cityID}'`
//    await connection.execute(sql); 
//   ctx.body = JSON.stringify("success");
// })
// //酒店推荐管理
// router.delete('/city/hotel/:pubID/:cityID',async(ctx,next) => {
//   let obj = ctx.params;
//   let pubID = obj.pubID;
//   let cityID = obj.cityID;
//   let sql = `DELETE FROM pub WHERE pubID = '${pubID}' and cityID = '${cityID}'`
//   console.log(sql)
//   await connection.execute(sql); 
//   ctx.body = JSON.stringify("success");
// })
// //帖子管理
// router.delete('/post/:shareID',async(ctx,next) => {
//   let obj = ctx.params;
//   let shareID = obj.shareID;
//   let sql = `DELETE FROM share WHERE shareID = '${shareID}'`
//   await connection.execute(sql); 
//   ctx.body = JSON.stringify("success");
// })



// /********************************查的操作**************************************/
// router.post('/get',async (ctx,next) => {
//   let obj = ctx.request.body;
//   let str = '';
//   delete obj.current;
//   delete obj.pageSize;
//   delete obj.sta
//   for(let i in obj){
//     str = str + `${i} Like'%${obj[i]}%' and `
//   }
//   let x = str.slice(0,str.length-5);
//   let sql = `select * from user where ${x}`;
//   console.log(x);
//   console.log(sql)
//   let res = await connection.execute(sql);
//   ctx.body = JSON.stringify(res[0]);
  
// })

// //城市文化列表推荐
// router.post('/city/citycul/get',async(ctx,next) => {
//   let obj = ctx.request.body;
//   let str = '';
//   delete obj.current;
//   delete obj.pageSize;
//   delete obj.sta
//   for(let i in obj){
//     str = str + `${i} Like'%${obj[i]}%' and `
//   }
//   let x = str.slice(0,str.length-5);
//   let sql = `select * from culture where ${x}`;
//   console.log(x);
//   console.log(sql)
//   let res = await connection.execute(sql);
//   ctx.body = JSON.stringify(res[0]);
  
// })
// //景点推荐
// router.post('/city/interest/get',async(ctx,next) => {
//   let obj = ctx.request.body;
//   let str = '';
//   delete obj.current;
//   delete obj.pageSize;
//   delete obj.sta
//   for(let i in obj){
//     str = str + `${i} Like'%${obj[i]}%' and `
//   }
//   let x = str.slice(0,str.length-5);
//   let sql = `select * from place where ${x}`;
//   console.log(x);
//   console.log(sql)
//   let res = await connection.execute(sql);
//   ctx.body = JSON.stringify(res[0]);
  
// })

// //周边推荐管理
// router.post('/city/enviro/get',async(ctx,next) => {
//   let obj = ctx.request.body;
//   let str = '';
//   delete obj.current;
//   delete obj.pageSize;
//   delete obj.sta
//   for(let i in obj){
//     str = str + `${i} Like'%${obj[i]}%' and `
//   }
//   let x = str.slice(0,str.length-5);
//   let sql = `select * from recommend where ${x}`;
//   console.log(x);
//   console.log(sql)
//   let res = await connection.execute(sql);
//   ctx.body = JSON.stringify(res[0]);
  
// })

// //美食推荐管理
// router.post('/city/food/get',async(ctx,next) => {
//   let obj = ctx.request.body;
//   let str = '';
//   delete obj.current;
//   delete obj.pageSize;
//   delete obj.sta
//   for(let i in obj){
//     str = str + `${i} Like'%${obj[i]}%' and `
//   }
//   let x = str.slice(0,str.length-5);
//   let sql = `select * from food where ${x}`;
//   console.log(x);
//   console.log(sql)
//   let res = await connection.execute(sql);
//   ctx.body = JSON.stringify(res[0]);
  
// })

// //酒店推荐管理
// router.post('/city/hotel/get',async(ctx,next) => {
//   let obj = ctx.request.body;
//   let str = '';
//   delete obj.current;
//   delete obj.pageSize;
//   delete obj.sta
//   for(let i in obj){
//     str = str + `${i} Like'%${obj[i]}%' and `
//   }
//   let x = str.slice(0,str.length-5);
//   let sql = `select * from pub where ${x}`;
//   console.log(x);
//   console.log(sql)
//   let res = await connection.execute(sql);
//   ctx.body = JSON.stringify(res[0]);
  
// })

// //帖子管理
// router.post('/post/get',async(ctx,next) => {
//   let obj = ctx.request.body;
//   let str = '';
//   delete obj.current;
//   delete obj.pageSize;
//   delete obj.sta
//   for(let i in obj){
//     str = str + `${i} Like'%${obj[i]}%' and `
//   }
//   let x = str.slice(0,str.length-5);
//   let sql = `select * from share where ${x}`;
//   console.log(x);
//   console.log(sql)
//   let res = await connection.execute(sql);
//   ctx.body = JSON.stringify(res[0]);
  
// })
// //系统管理
// router.post('/admin/get',async(ctx,next) => {
//   let obj = ctx.request.body;
//   let str = '';
//   delete obj.current;
//   delete obj.pageSize;
//   delete obj.sta
//   for(let i in obj){
//     str = str + `${i} Like'%${obj[i]}%' and `
//   }
//   let x = str.slice(0,str.length-5);
//   let sql = `select * from admin where ${x}`;
//   console.log(x);
//   console.log(sql)
//   let res = await connection.execute(sql);
//   ctx.body = JSON.stringify(res[0]);

// })
// /*****************登录验证******************/
// router.post('/login',async (ctx,next) => {
//   const admin = ctx.request.body;
//   let count = 0;
//   console.log(admin)
//   let sql = 'SELECT * FROM admin';
//   let res = await connection.execute(sql);
//   let array = [];
//   let record;
//   let turn;
//   for(var i in res[0]){
//     let obj = {
//       adminName:res[0][i].adminName,
//       passwd:res[0][i].passwd
//     }
//     array.push(obj)
//   }
//   for(var i in res[0]){
//     if(res[0][i].adminName == admin.username && res[0][i].passwd == admin.password){
//       count++;
//       record = i;
//     }
//     else{
//       count+=0;
//     }
//   }
//   if(count){
//     turn = {
//       status:'success',
//       data:{
//         username:res[0][record].adminName,
//         adminImg:res[0][record].adminImg
//       }
//     }
//   }else{
//     turn = {status:'fail'}
//   }

//   ctx.body = JSON.stringify(turn);
// })
  
// /*************************更新-改***************************/
// //用户
// router.put('/',async (ctx,next) => {
//   let result = ctx.request.body;
//   let newObj = result.new;
//   let id = result.old.Userwxno;
//   console.log(id)
//   let str = '';
//   for(let i in newObj){
//     str = str + `${i} = '${newObj[i]}',`
//   }
//   let x = str.slice(0,str.length-1);
//   let sql = `UPDATE user SET ${x} WHERE Userwxno = '${id}'`
//   let res = await connection.execute(sql)
//   ctx.body = JSON.stringify('success!')
// })


// //城市文化列表
// router.put('/city/citycul',async(ctx,next)=>{
//   let result = ctx.request.body;
//   let newObj = result.new;
//   let id0 = result.old.cultureID;
//   let id1 = result.old.cityID;
//   let str = '';
//   for(let i in newObj){
//     str = str + `${i} = '${newObj[i]}',`
//   }
//   let x = str.slice(0,str.length-1);
//   let sql = `UPDATE culture SET ${x} WHERE cultureID = '${id0}' and cityID = '${id1}'`
//   let res = await connection.execute(sql)
//   ctx.body = JSON.stringify('success!')
// })

// //景点推荐管理
// router.put('/city/interest',async(ctx,next)=>{
//   let result = ctx.request.body;
//   let newObj = result.new;
//   let id0 = result.old.placeID;
//   let id1 = result.old.cityID;
//   let str = '';
//   for(let i in newObj){
//     str = str + `${i} = '${newObj[i]}',`
//   }
//   let x = str.slice(0,str.length-1);
//   let sql = `UPDATE place SET ${x} WHERE placeID = '${id0}' and cityID = '${id1}' `
//   console.log(sql)
//   let res = await connection.execute(sql)
//   ctx.body = JSON.stringify('success!')
// })

// //周边
// router.put('/city/enviro',async(ctx,next)=>{
//   let result = ctx.request.body;
//   let newObj = result.new;
//   let id0 = result.old.recomID;
//   let id1 = result.old.cityID;
//   let str = '';
//   for(let i in newObj){
//     str = str + `${i} = '${newObj[i]}',`
//   }
//   let x = str.slice(0,str.length-1);
//   let sql = `UPDATE recommend SET ${x} WHERE recomID = '${id0}' and cityID = '${id1}'`
//   let res = await connection.execute(sql)
//   ctx.body = JSON.stringify('success!')
// })
// //美食
// router.put('/city/food',async(ctx,next)=>{
//   let result = ctx.request.body;
//   let newObj = result.new;
//   let id0 = result.old.foodID;
//   let id1 = result.old.cityID;
//   let str = '';
//   for(let i in newObj){
//     str = str + `${i} = '${newObj[i]}',`
//   }
//   let x = str.slice(0,str.length-1);
//   let sql = `UPDATE food SET ${x} WHERE foodID = '${id0}' and cityID = '${id1}'`
//   console.log(sql)
//   let res = await connection.execute(sql)
//   ctx.body = JSON.stringify('success!')
// })
// //酒店
// router.put('/city/pub',async(ctx,next)=>{
//   let result = ctx.request.body;
//   let newObj = result.new;
//   let id0 = result.old.pubID;
//   let id1 = result.old.cityID;
//   let str = '';
//   for(let i in newObj){
//     str = str + `${i} = '${newObj[i]}',`
//   }
//   let x = str.slice(0,str.length-1);
//   let sql = `UPDATE pub SET ${x} WHERE pubID = '${id0}' and cityID = '${id1}'`
//   let res = await connection.execute(sql)
//   ctx.body = JSON.stringify('success!')
// })

// //图片的action
// router.post('/pic',async(ctx,next) => {
//   const file = ctx.request.files.upload;
//   const reader = fs.createReadStream(file.path);
//   const filePath = path.join(__dirname,'images') + `\\${file.name}`;
//   const upStream = fs.createWriteStream(filePath);
//   reader.pipe(upStream)
//   let pic = filePath.slice();
//   let realPath = `http://127.0.0.1:8080/images/${file.name}`
//   console.log(realPath)
//   ctx.body = {Type:200,status:'上传成功',addr:realPath}
// })

// router.get('/images/:name',async(ctx,next) => {
//   let name = ctx.params.name;
//   let filePath = `./images/${name}`
//   let data = fs.readFileSync(filePath);
//   let mimeType = mime.lookup(filePath);
//   ctx.set('content-type',mimeType);
//   ctx.body = data;
// })


app.use(router.routes())
app.listen(8080);